database restoration problem- data became incorrect caused byincorrect date in the server

am 09.04.2010 14:22:20 von Khangelani Gama

--_000_D78A8169F9436B4DB978300336168F3B3359A3A9F7SWBREXCH00u cs_
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

Hi all

I am a junior DBA:

Please give an advice on how I should solve the following problem,

We are still using 7.3.4 PostgreSQL but will be upgrading very soon, databa=
se is running on CentOS 5 O/S .

Problem: - it's about the wrong data in the system:

Everything was fine until a certain process(for financial period processing=
) that normally generates many transactions in the database took place when=
the server lost the date. The date changed from 07-04-2010 to 15-08-2003..=
..hence the process ran until it finished which leaded to the generation of =
the wrong data.

When we thought of restoring from a successful dump file(backup file) for t=
he 07-04-2010 so that we could restore it to have everything before the dat=
e changed, we were not lucky because the only latest successful dump file w=
as on the 03/04/2010.

Question:
How can we removed everything that got processed on the wrong date and have=
everything restored so that it's in the correct state as at 07/04/2010... =
I tried to search for a solution but I can't find anything.

Your help would be much appreciated

Thanks
Khangelani

Confidentiality Notice:http://ucs.co.za/conf.html

________________________________
The contents of and attachments to this e-mail are intended for the address=
ee only, and may contain the confidential information of UCS Group and/or i=
ts subsidiaries. Any review, use or dissemination thereof by anyone other t=
han the intended addressee is prohibited. If you are not the intended addre=
ssee please notify the writer immediately and destroy the e-mail. UCS Group=
Limited and its subsidiaries distance themselves from and accept no liabil=
ity for unauthorised use of their e-mail facilities or e-mails sent other t=
han strictly for business purposes.

--_000_D78A8169F9436B4DB978300336168F3B3359A3A9F7SWBREXCH00u cs_
Content-Type: text/html; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

osoft-com:office:office" xmlns:w=3D"urn:schemas-microsoft-com:office:word" =
xmlns:x=3D"urn:schemas-microsoft-com:office:excel" xmlns:p=3D"urn:schemas-m=
icrosoft-com:office:powerpoint" xmlns:a=3D"urn:schemas-microsoft-com:office=
:access" xmlns:dt=3D"uuid:C2F41010-65B3-11d1-A29F-00AA00C14882" xmlns:s=3D"=
uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882" xmlns:rs=3D"urn:schemas-microsof=
t-com:rowset" xmlns:z=3D"#RowsetSchema" xmlns:b=3D"urn:schemas-microsoft-co=
m:office:publisher" xmlns:ss=3D"urn:schemas-microsoft-com:office:spreadshee=
t" xmlns:c=3D"urn:schemas-microsoft-com:office:component:spread sheet" xmlns=
:odc=3D"urn:schemas-microsoft-com:office:odc" xmlns:oa=3D"urn:schemas-micro=
soft-com:office:activation" xmlns:html=3D"http://www.w3.org/TR/REC-html40" =
xmlns:q=3D"http://schemas.xmlsoap.org/soap/envelope/" xmlns:rtc=3D"http://m=
icrosoft.com/officenet/conferencing" xmlns:D=3D"DAV:" xmlns:Repl=3D"http://=
schemas.microsoft.com/repl/" xmlns:mt=3D"http://schemas.microsoft.com/share=
point/soap/meetings/" xmlns:x2=3D"http://schemas.microsoft.com/office/excel=
/2003/xml" xmlns:ppda=3D"http://www.passport.com/NameSpace.xsd" xmlns:ois=
=3D"http://schemas.microsoft.com/sharepoint/soap/ois/" xmlns:dir=3D"http://=
schemas.microsoft.com/sharepoint/soap/directory/" xmlns:ds=3D"http://www.w3=
..org/2000/09/xmldsig#" xmlns:dsp=3D"http://schemas.microsoft.com/sharepoint=
/dsp" xmlns:udc=3D"http://schemas.microsoft.com/data/udc" xmlns:xsd=3D"http=
://www.w3.org/2001/XMLSchema" xmlns:sub=3D"http://schemas.microsoft.com/sha=
repoint/soap/2002/1/alerts/" xmlns:ec=3D"http://www.w3.org/2001/04/xmlenc#"=
xmlns:sp=3D"http://schemas.microsoft.com/sharepoint/" xmlns:sps=3D"http://=
schemas.microsoft.com/sharepoint/soap/" xmlns:xsi=3D"http://www.w3.org/2001=
/XMLSchema-instance" xmlns:udcs=3D"http://schemas.microsoft.com/data/udc/so=
ap" xmlns:udcxf=3D"http://schemas.microsoft.com/data/udc/xmlfile " xmlns:udc=
p2p=3D"http://schemas.microsoft.com/data/udc/parttopart" xmlns:wf=3D"http:/=
/schemas.microsoft.com/sharepoint/soap/workflow/" xmlns:dsss=3D"http://sche=
mas.microsoft.com/office/2006/digsig-setup" xmlns:dssi=3D"http://schemas.mi=
crosoft.com/office/2006/digsig" xmlns:mdssi=3D"http://schemas.openxmlformat=
s.org/package/2006/digital-signature" xmlns:mver=3D"http://schemas.openxmlf=
ormats.org/markup-compatibility/2006" xmlns:m=3D"http://schemas.microsoft.c=
om/office/2004/12/omml" xmlns:mrels=3D"http://schemas.openxmlformats.org/pa=
ckage/2006/relationships" xmlns:spwp=3D"http://microsoft.com/sharepoint/web=
partpages" xmlns:ex12t=3D"http://schemas.microsoft.com/exchange/service s/20=
06/types" xmlns:ex12m=3D"http://schemas.microsoft.com/exchange/service s/200=
6/messages" xmlns:pptsl=3D"http://schemas.microsoft.com/sharepoint/soap/ Sli=
deLibrary/" xmlns:spsl=3D"http://microsoft.com/webservices/SharePointPor tal=
Server/PublishedLinksService" xmlns:Z=3D"urn:schemas-microsoft-com:" xmlns:=
st=3D"" xmlns=3D"http://www.w3.org/TR/REC-html40">

>

Hi all

I am a junior DBA:

Please give an advice on how I should solve the foll=
owing problem,

We are still using 7.3.4 PostgreSQL but will be upgr=
ading very soon, database is running on CentOS 5 O/S .

Problem: - it’s about the wrong data in the=
system:

Everything was fine until a certain process(for fina=
ncial period processing) that normally generates many transactions in the d=
atabase took place when the server lost the date. The date changed from 07-=
04-2010 to 15-08-2003…hence the process
ran until it finished which leaded to the generation of the wrong data. :p>

When we thought of restoring from a successful dump =
file(backup file) for the 07-04-2010 so that we could restore it to have ev=
erything before the date changed, we were not lucky because the only latest=
successful dump file was on the 03/04/2010.

Question:

How can we removed everything that got processed on =
the wrong date and have everything restored so that it’s in the corre=
ct state as at 07/04/2010… I tried to search for a solution but I can=
’t find anything.

Your help would be much appreciated

Thanks

Khangelani

family:"Courier New"">

Confidentiality Notice: =3D"http://ucs.co.za/conf.html">http://ucs.co.za=
/conf.html

The contents of and attachme=
nts to this e-mail are intended for the addressee only, and may contain the=
confidential information of UCS Group and/or its subsidiaries. Any review,=
use or dissemination thereof by anyone
other than the intended addressee is prohibited. If you are not the intend=
ed addressee please notify the writer immediately and destroy the e-mail. U=
CS Group Limited and its subsidiaries distance themselves from and accept n=
o liability for unauthorised use
of their e-mail facilities or e-mails sent other than strictly for busines=
s purposes.

--_000_D78A8169F9436B4DB978300336168F3B3359A3A9F7SWBREXCH00u cs_--

Re: database restoration problem- data became incorrect caused byincorrect date in the server

am 09.04.2010 14:41:01 von Khangelani Gama

--_000_D78A8169F9436B4DB978300336168F3B3359A3AA3BSWBREXCH00u cs_
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

To add on to previous comments:

There is only one database in the server and the backup server also has inc=
orrect data because all the wrong data replicated to the backup server.

From: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-owner@postgresql=
..org] On Behalf Of Khangelani Gama
Sent: Friday, April 09, 2010 2:22 PM
To: pgsql-admin@postgresql.org
Subject: [ADMIN] database restoration problem- data became incorrect caused=
by incorrect date in the server

Hi all

I am a junior DBA:

Please give an advice on how I should solve the following problem,

We are still using 7.3.4 PostgreSQL but will be upgrading very soon, databa=
se is running on CentOS 5 O/S .

Problem: - it's about the wrong data in the system:

Everything was fine until a certain process(for financial period processing=
) that normally generates many transactions in the database took place when=
the server lost the date. The date changed from 07-04-2010 to 15-08-2003..=
..hence the process ran until it finished which leaded to the generation of =
the wrong data.

When we thought of restoring from a successful dump file(backup file) for t=
he 07-04-2010 so that we could restore it to have everything before the dat=
e changed, we were not lucky because the only latest successful dump file w=
as on the 03/04/2010.

Question:
How can we removed everything that got processed on the wrong date and have=
everything restored so that it's in the correct state as at 07/04/2010... =
I tried to search for a solution but I can't find anything.

Your help would be much appreciated

Thanks
Khangelani

Confidentiality Notice:http://ucs.co.za/conf.html

________________________________
The contents of and attachments to this e-mail are intended for the address=
ee only, and may contain the confidential information of UCS Group and/or i=
ts subsidiaries. Any review, use or dissemination thereof by anyone other t=
han the intended addressee is prohibited. If you are not the intended addre=
ssee please notify the writer immediately and destroy the e-mail. UCS Group=
Limited and its subsidiaries distance themselves from and accept no liabil=
ity for unauthorised use of their e-mail facilities or e-mails sent other t=
han strictly for business purposes.

________________________________
The contents of and attachments to this e-mail are intended for the address=
ee only, and may contain the confidential information of UCS Group and/or i=
ts subsidiaries. Any review, use or dissemination thereof by anyone other t=
han the intended addressee is prohibited. If you are not the intended addre=
ssee please notify the writer immediately and destroy the e-mail. UCS Group=
Limited and its subsidiaries distance themselves from and accept no liabil=
ity for unauthorised use of their e-mail facilities or e-mails sent other t=
han strictly for business purposes.

--_000_D78A8169F9436B4DB978300336168F3B3359A3AA3BSWBREXCH00u cs_
Content-Type: text/html; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

osoft-com:office:office" xmlns:w=3D"urn:schemas-microsoft-com:office:word" =
xmlns:x=3D"urn:schemas-microsoft-com:office:excel" xmlns:p=3D"urn:schemas-m=
icrosoft-com:office:powerpoint" xmlns:a=3D"urn:schemas-microsoft-com:office=
:access" xmlns:dt=3D"uuid:C2F41010-65B3-11d1-A29F-00AA00C14882" xmlns:s=3D"=
uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882" xmlns:rs=3D"urn:schemas-microsof=
t-com:rowset" xmlns:z=3D"#RowsetSchema" xmlns:b=3D"urn:schemas-microsoft-co=
m:office:publisher" xmlns:ss=3D"urn:schemas-microsoft-com:office:spreadshee=
t" xmlns:c=3D"urn:schemas-microsoft-com:office:component:spread sheet" xmlns=
:odc=3D"urn:schemas-microsoft-com:office:odc" xmlns:oa=3D"urn:schemas-micro=
soft-com:office:activation" xmlns:html=3D"http://www.w3.org/TR/REC-html40" =
xmlns:q=3D"http://schemas.xmlsoap.org/soap/envelope/" xmlns:rtc=3D"http://m=
icrosoft.com/officenet/conferencing" xmlns:D=3D"DAV:" xmlns:Repl=3D"http://=
schemas.microsoft.com/repl/" xmlns:mt=3D"http://schemas.microsoft.com/share=
point/soap/meetings/" xmlns:x2=3D"http://schemas.microsoft.com/office/excel=
/2003/xml" xmlns:ppda=3D"http://www.passport.com/NameSpace.xsd" xmlns:ois=
=3D"http://schemas.microsoft.com/sharepoint/soap/ois/" xmlns:dir=3D"http://=
schemas.microsoft.com/sharepoint/soap/directory/" xmlns:ds=3D"http://www.w3=
..org/2000/09/xmldsig#" xmlns:dsp=3D"http://schemas.microsoft.com/sharepoint=
/dsp" xmlns:udc=3D"http://schemas.microsoft.com/data/udc" xmlns:xsd=3D"http=
://www.w3.org/2001/XMLSchema" xmlns:sub=3D"http://schemas.microsoft.com/sha=
repoint/soap/2002/1/alerts/" xmlns:ec=3D"http://www.w3.org/2001/04/xmlenc#"=
xmlns:sp=3D"http://schemas.microsoft.com/sharepoint/" xmlns:sps=3D"http://=
schemas.microsoft.com/sharepoint/soap/" xmlns:xsi=3D"http://www.w3.org/2001=
/XMLSchema-instance" xmlns:udcs=3D"http://schemas.microsoft.com/data/udc/so=
ap" xmlns:udcxf=3D"http://schemas.microsoft.com/data/udc/xmlfile " xmlns:udc=
p2p=3D"http://schemas.microsoft.com/data/udc/parttopart" xmlns:wf=3D"http:/=
/schemas.microsoft.com/sharepoint/soap/workflow/" xmlns:dsss=3D"http://sche=
mas.microsoft.com/office/2006/digsig-setup" xmlns:dssi=3D"http://schemas.mi=
crosoft.com/office/2006/digsig" xmlns:mdssi=3D"http://schemas.openxmlformat=
s.org/package/2006/digital-signature" xmlns:mver=3D"http://schemas.openxmlf=
ormats.org/markup-compatibility/2006" xmlns:m=3D"http://schemas.microsoft.c=
om/office/2004/12/omml" xmlns:mrels=3D"http://schemas.openxmlformats.org/pa=
ckage/2006/relationships" xmlns:spwp=3D"http://microsoft.com/sharepoint/web=
partpages" xmlns:ex12t=3D"http://schemas.microsoft.com/exchange/service s/20=
06/types" xmlns:ex12m=3D"http://schemas.microsoft.com/exchange/service s/200=
6/messages" xmlns:pptsl=3D"http://schemas.microsoft.com/sharepoint/soap/ Sli=
deLibrary/" xmlns:spsl=3D"http://microsoft.com/webservices/SharePointPor tal=
Server/PublishedLinksService" xmlns:Z=3D"urn:schemas-microsoft-com:" xmlns:=
st=3D"" xmlns=3D"http://www.w3.org/TR/REC-html40">

>

To add on to previous =
comments:

There is only one data=
base in the server and the backup server also has incorrect data because al=
l the wrong data replicated to the backup server.

0in 0in">

;Tahoma","sans-serif"">From: ize:10.0pt;font-family:"Tahoma","sans-serif""> pgsql-ad=
min-owner@postgresql.org [mailto:pgsql-admin-owner@postgresql.org]
On Behalf Of Khangelani Gama

Sent: Friday, April 09, 2010 2:22 PM

To: pgsql-admin@postgresql.org

Subject: [ADMIN] database restoration problem- data became incorrect=
caused by incorrect date in the server

Hi all

I am a junior DBA:

Please give an advice on how I should solve the foll=
owing problem,

We are still using 7.3.4 PostgreSQL but will be upgr=
ading very soon, database is running on CentOS 5 O/S .

Problem: - it’s about the wrong data in the=
system:

Question:

Your help would be much appreciated

Thanks

Khangelani

family:"Courier New"">

Confidentiality Notice: =3D"http://ucs.co.za/conf.html">http://ucs.co.za/conf.html span>

mes New Roman","serif"">

style=3D"font-size:12.0pt;font-family:"Times New Roman","se=
rif"">

al","sans-serif";
color:gray">The contents of and attachments to this e-mail are intended for=
the addressee only, and may contain the confidential information of UCS Gr=
oup and/or its subsidiaries.
Any review, use or dissemination thereof by anyone other than the intended=
addressee is prohibited. If you are not the intended addressee please noti=
fy the writer immediately and destroy the e-mail. UCS Group Limited and its=
subsidiaries distance themselves
from and accept no liability for unauthorised use of their e-mail faciliti=
es or e-mails sent other than strictly for business purposes. tyle=3D"font-size:12.0pt;
font-family:"Times New Roman","serif""> n>

Re: database restoration problem- data became

am 09.04.2010 16:14:27 von Kevin Grittner

Khangelani Gama wrote:

> We are still using 7.3.4 PostgreSQL

Do you really mean that, or is that a typo?

What sort of backups do you have? (pg_dump, file system copy, etc.)

-Kevin

--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: Re: database restoration problem- data became

am 09.04.2010 19:43:40 von Khangelani Gama

Hi

I really mean it....the backups we have uses pg_dump command. The automatic=
backup dumps file happen daily and gets saved in a certain directory

But the issue now is that we don't have any backup file for us to restore t=
he database but exclude every data that got create when the date in server =
was incorrect.

Thanks

-----Original Message-----
From: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-owner@postgresql=
..org] On Behalf Of Kevin Grittner
Sent: Friday, April 09, 2010 4:14 PM
To: pgsql-admin@postgresql.org; Khangelani Gama
Subject: [ADMIN] Re: database restoration problem- data became incorrect ca=
used by incorrect date in the server

Khangelani Gama wrote:

> We are still using 7.3.4 PostgreSQL

Do you really mean that, or is that a typo?

What sort of backups do you have? (pg_dump, file system copy, etc.)

-Kevin

--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

The contents of and attachments to this e-mail are intended for the address=
ee only, and may contain the confidential information of UCS Group and/or i=
ts subsidiaries. Any review, use or dissemination thereof by anyone other =
than the intended addressee is prohibited. If you are not the intended add=
ressee please notify the writer immediately and destroy the e-mail. UCS Gr=
oup Limited and its subsidiaries distance themselves from and accept no lia=
bility for unauthorised use of their e-mail facilities or e-mails sent othe=
r than strictly for business purposes.

--=20
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: Re: database restoration problem- data became

am 09.04.2010 20:03:03 von Kevin Grittner

Khangelani Gama wrote:

> the backups we have uses pg_dump command. The automatic
> backup dumps file happen daily and gets saved in a certain
> directory

No off-site copies or archives to fall back on? If not, my ideas
are:

(1) I hope you made a file copy as soon as you found the problem.
If not, stop the database and make one immediately. If the old
versions of the rows haven't yet been vacuumed away, it might be
possible to fish some or all of the corrupted data out of the
database. This is very tricky work, though, and you might want to
contract with an expert. (I don't do that anymore, but there are
several good companies with people who do.)

(2) Are there any source documents you can fall back on to
reconstruct the data?

(3) Is there any way to "reverse" the particular calculations or
operations which mangled the data?

Once you do get upgraded, you should look into Point In Time
Recovery (PITR) backups. Had you been running this, you could have
replayed the transactions right up to the moment of corruption.
Well, if you kept a base backup from before the corruption and all
the WAL files since that base backup.

-Kevin

--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: Re: database restoration problem- data became incorrect caused by incorrect date in the server

am 10.04.2010 07:37:25 von Khangelani Gama

Thanks for the ideas

-----Original Message-----
From: Kevin Grittner [mailto:Kevin.Grittner@wicourts.gov]
Sent: Friday, April 09, 2010 8:03 PM
To: pgsql-admin@postgresql.org; Khangelani Gama
Subject: RE: [ADMIN] Re: database restoration problem- data became incorrec=
t caused by incorrect date in the server

Khangelani Gama wrote:

> the backups we have uses pg_dump command. The automatic
> backup dumps file happen daily and gets saved in a certain
> directory

No off-site copies or archives to fall back on? If not, my ideas
are:

(1) I hope you made a file copy as soon as you found the problem.
If not, stop the database and make one immediately. If the old
versions of the rows haven't yet been vacuumed away, it might be
possible to fish some or all of the corrupted data out of the
database. This is very tricky work, though, and you might want to
contract with an expert. (I don't do that anymore, but there are
several good companies with people who do.)

(2) Are there any source documents you can fall back on to
reconstruct the data?

(3) Is there any way to "reverse" the particular calculations or
operations which mangled the data?

Once you do get upgraded, you should look into Point In Time
Recovery (PITR) backups. Had you been running this, you could have
replayed the transactions right up to the moment of corruption.
Well, if you kept a base backup from before the corruption and all
the WAL files since that base backup.

-Kevin

The contents of and attachments to this e-mail are intended for the address=
ee only, and may contain the confidential information of UCS Group and/or i=
ts subsidiaries. Any review, use or dissemination thereof by anyone other =
than the intended addressee is prohibited. If you are not the intended add=
ressee please notify the writer immediately and destroy the e-mail. UCS Gr=
oup Limited and its subsidiaries distance themselves from and accept no lia=
bility for unauthorised use of their e-mail facilities or e-mails sent othe=
r than strictly for business purposes.

--=20
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin